CREATE DEFINER=`computraceqa`@`%` PROCEDURE `computraceqa`.`GetSessionsChart`(
    IN p_emp_code VARCHAR(100),
    IN p_started_on TIMESTAMP,
    IN p_ended_on TIMESTAMP, 
    IN p_device VARCHAR(100),
    IN ChartType VARCHAR(100)
)
BEGIN
    -- Check if p_started_on and p_ended_on are the same
    IF (p_started_on IS NOT NULL AND p_ended_on IS NOT NULL AND p_started_on = p_ended_on) THEN
        -- Add one day to p_ended_on
        SET p_ended_on = DATE_ADD(p_ended_on, INTERVAL 1 DAY);
    END IF;

    SELECT 
        CAST(login_time AS DATE) AS LoginDate,
        SUM(CASE WHEN lower(system_status) = 'active' THEN 1 ELSE 0 END) AS ActiveCount,
        SUM(CASE WHEN lower(system_status) = 'sleep' THEN 1 ELSE 0 END) AS SleepCount,
        SUM(CASE WHEN lower(system_status) = 'idle' THEN 1 ELSE 0 END) AS IdleCount,
        COUNT(*) AS TotalCount
    FROM 
        system_login_details  
    WHERE 
        (p_started_on IS NULL OR login_time >= p_started_on)
        AND (p_ended_on IS NULL OR login_time <= p_ended_on)
        AND (p_emp_code IS NULL OR lower(emp_code) LIKE CONCAT('%', lower(p_emp_code), '%'))
        AND (lower(system_status) = lower(ChartType) OR ChartType IS NULL)
    GROUP BY 
        CAST(login_time AS DATE);

END